# -*- coding: utf-8 -*-#
__author__ = 'yangq'
import os
from tornado import gen
from common import timetool
from appcommon.appinstance import mysql_app_pool
from user import models as userm


@gen.coroutine
def get_school_list(area):

    sql = (
        u"select area, school from driver_school where area = '{}' "
    )
    res = yield mysql_app_pool.get_all(sql.format(area), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def set_couch_base_info(phone, nick_name, sex, tel, years, car_type):
    sql = (
        u"select * from couch_base_info where phone='{}'"
    )
    has_info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if has_info:
        sql = (
            u"update couch_base_info set nick_name = '{}', sex='{}', tel='{}',"
            u"years='{}', car_type='{}' where phone='{}' "
        )
        yield mysql_app_pool.update(sql.format(nick_name, sex, tel, years, car_type, phone))
    else:
        sql = (
            u"insert into couch_base_info (phone, nick_name, sex, tel, years, car_type) "
            u"values ('{}', '{}', '{}', '{}', '{}', '{}')"
        )
        yield mysql_app_pool.update(sql.format(phone, nick_name, sex, tel, years, car_type))
    raise gen.Return(1)


@gen.coroutine
def set_couch_school(phone, area, school):
    sql = (
        u"select * from couch_base_info where phone='{}'"
    )
    has_info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if has_info:
        sql = (
            u"update couch_base_info set area = '{}', school='{}' where phone='{}' "
        )
        yield mysql_app_pool.update(sql.format(area, school, phone))
    else:
        sql = (
            u"insert into couch_base_info (phone, area, school) "
            u"values ('{}', '{}', '{}')"
        )
        yield mysql_app_pool.update(sql.format(phone, area, school))
    raise gen.Return(1)


@gen.coroutine
def set_couch_drive_addr(phone, area_str, longitude, latitude, area):
    sql = (
        u"select * from couch_driver_addr where phone='{}'"
    )
    has_info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if has_info:
        sql = (
            u"update couch_driver_addr set area_str='{}', longitude='{}',latitude='{}', area='{}' "
            u" where phone='{}' "
        )
        yield mysql_app_pool.update(sql.format(area_str, longitude, latitude, area, phone))
    else:
        sql = (
            u"insert into couch_driver_addr (phone, area_str, longitude, latitude, area) "
            u"values ('{}', '{}', '{}', '{}', '{}')"
        )
        yield mysql_app_pool.update(sql.format(phone, area_str, longitude, latitude, area))
    raise gen.Return(1)


@gen.coroutine
def set_couch_introduce(phone, days, points, introduce):
    sql = (
        u"select * from couch_introduce where phone='{}'"
    )
    has_info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if has_info:
        sql = (
            u"update couch_introduce set days={}, points='{}',introduce='{}' "
            u"where phone='{}' "
        )
        yield mysql_app_pool.update(sql.format(days, points, introduce, phone))
    else:
        sql = (
            u"insert into couch_introduce (phone, days, points, introduce) "
            u"values ('{}', {}, '{}', '{}')"
        )
        yield mysql_app_pool.update(sql.format(phone, days, points, introduce))
    raise gen.Return(1)


@gen.coroutine
def set_class_info(phone, class_id, class_name, car_type, price, desc):
    if class_id:
        sql = (
            u"update couch_class_info set class_name='{}', car_type='{}',price={}, `desc`='{}' "
            u"where id={} "
        )
        yield mysql_app_pool.update(sql.format(class_name, car_type, price, desc, class_id))
    else:
        sql = (
            u"insert into couch_class_info (phone, class_name, car_type, price, `desc`) "
            u"values ('{}', '{}', '{}', {}, '{}')"
        )
        yield mysql_app_pool.insert_one(sql.format(phone, class_name, car_type, price, desc))
    # 更新最低价格
    base_sql = (
        u"select * from couch_base_info where phone = '{}'"
    )
    base_info = yield mysql_app_pool.get_one(base_sql.format(phone), cursor='dict')
    if base_info:
        if price < base_info['price']:
            sql_u = (
                u"update couch_base_info set price={} where phone='{}'"
            )
            yield mysql_app_pool.insert_one(sql_u.format(price, phone))
    else:
        sql_i = (
            u"insert into couch_base_info (phone, price) values ('{}', {})"
        )
        yield mysql_app_pool.insert_one(sql_i.format(phone, price))

    raise gen.Return(1)


@gen.coroutine
def del_class_info(class_id):
    sql = (
        u"delete from couch_class_info where id = {}"
    )
    yield mysql_app_pool.delete(sql.format(class_id))
    raise gen.Return(1)


@gen.coroutine
def check_couch_can_create_card(phone):
    base_info = yield get_couch_base_info(phone)
    if not base_info:
        raise gen.Return(0)
    addr_info = yield get_driver_addr(phone)
    if not addr_info:
        raise gen.Return(0)
    introduce_info = yield get_couch_introduce(phone)
    if not introduce_info:
        raise gen.Return(0)
    class_info = yield get_class_info_list(phone)
    if not class_info:
        raise gen.Return(0)
    raise gen.Return(1)


@gen.coroutine
def save_couch_score_log(phone, desc, type, score):
    current_time = timetool.get_timestamp()
    sql = (
        "insert into couch_score_log (phone, `type`, `desc`, score, pubtime) values ("
        u"'{}', '{}', '{}', {}, {})"
    )
    yield mysql_app_pool.update(sql.format(phone, type, desc, score, current_time))
    raise gen.Return(1)


@gen.coroutine
def update_couch_score_2_mysql(phone, score):
    sql = (
        u"select * from couch_base_info where phone='{}'"
    )
    old_score = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')

    if old_score:
        if not old_score['score']:
            old_score['score'] = 0
        news_score = old_score['score'] + score
        sql = (
            u"update couch_base_info set score = {} where phone='{}' "
        )
        yield mysql_app_pool.update(sql.format(news_score, phone))
    else:
        news_score = score
        sql = (
            u"insert into couch_base_info (phone, score) values ('{}', {})"
        )
        yield mysql_app_pool.update(sql.format(phone, news_score))
    add = 1
    raise gen.Return(add)


@gen.coroutine
def create_couch_card(phone):
    sql = (
        u"update couch_base_info set has_card = 1 where phone = '{}'"
    )
    yield mysql_app_pool.update(sql.format(phone))
    raise gen.Return(1)


@gen.coroutine
def check_couch_has_card(phone):
    sql = (
        u"select has_card from couch_base_info where phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if info:
        res = info['has_card']
    else:
        res = 0
    raise gen.Return(res)


@gen.coroutine
def check_is_follow(phone, couch_phone):
    sql = (
        u"select 1 from couch_follow where follow_phone = '{}' and couch_phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone, couch_phone))
    res = 1 if info else 0
    raise gen.Return(res)


@gen.coroutine
def check_is_student(phone):
    sql = (
        u"select 1 from couch_follow where follow_phone = '{}' "
    )
    info = yield mysql_app_pool.get_one(sql.format(phone))
    res = 1 if info else 0
    raise gen.Return(res)


@gen.coroutine
def follow_couch(phone, couch_phone):
    current_time = timetool.get_timestamp()
    sql = (
        u"insert into couch_follow (couch_phone, follow_phone, add_time) values ("
        u"'{}', '{}', {})"
    )
    res = yield mysql_app_pool.insert_one(sql.format(couch_phone, phone, current_time))

    base_sql = (
        u"update couch_base_info set followers = followers + 1 where phone = '{}'"
    )
    yield mysql_app_pool.update(base_sql.format(couch_phone))
    raise gen.Return(res)


@gen.coroutine
def chech_student_followed(phone, couch_phone):
    sql = (
        u"select 1 from couch_score_log where phone = '{}' and `desc` = '{}' and type='follow'"
    )
    info = yield mysql_app_pool.get_one(sql.format(couch_phone, phone))
    res = 1 if info else 0
    raise gen.Return(res)


@gen.coroutine
def cancel_follow_couch(phone, couch_phone):
    sql = (
        u"delete from couch_follow where follow_phone = '{}' and couch_phone = '{}'"
    )
    res = yield mysql_app_pool.delete(sql.format(phone, couch_phone))

    base_sql = (
        u"update couch_base_info set followers = followers - 1 where phone = '{}'"
    )
    yield mysql_app_pool.update(base_sql.format(couch_phone))
    raise gen.Return(res)


@gen.coroutine
def evaluate_couch(phone, couch_phone, stars, content):
    current_time = timetool.get_timestamp()
    sql = (
        u"insert into couch_evaluate (couch_phone, student_phone, stars, content, add_time) values ("
        u"'{}', '{}', {}, '{}', {})"
    )
    res = yield mysql_app_pool.insert_one(sql.format(couch_phone, phone, stars, content, current_time))
    stars_sql = (
        u"select sum(stars) as all_stars, count(*) as cnt from couch_evaluate "
        "where couch_phone = '{}' "
    )
    stars_info = yield mysql_app_pool.get_one(stars_sql.format(couch_phone), cursor='dict')
    if stars_info:
        avg_stars = stars_info['all_stars'] / stars_info['cnt']
    else:
        avg_stars = 0
    set_sql = "update couch_base_info set stars = {} where phone = '{}'"
    yield mysql_app_pool.update(set_sql.format(avg_stars, couch_phone))
    raise gen.Return(res)


@gen.coroutine
def save_student_consult(phone, couch_phone, class_id, name, tel, addr, content):
    current_time = timetool.get_timestamp()
    sql = (
        u"insert into student_consult (phone, couch_phone, class_id, `name`, tel, addr, content, add_time) "
        u"values ('{}', '{}', '{}', '{}', '{}', '{}', '{}', {})"
    )
    res = yield mysql_app_pool.insert_one(sql.format(
        phone, couch_phone, class_id, name, tel, addr, content, current_time))
    raise gen.Return(res)


@gen.coroutine
def save_pub_consult(phone, car_type, name, tel, addr, content, area):
    current_time = timetool.get_timestamp()
    sql = (
        u"insert into pub_consult (phone, car_type, `name`, tel, addr, content, add_time, area) "
        u"values ('{}', '{}', '{}', '{}', '{}', '{}', {}, '{}')"
    )
    res = yield mysql_app_pool.insert_one(sql.format(
        phone, car_type, name, tel, addr, content, current_time, area))
    raise gen.Return(res)


@gen.coroutine
def get_couch_auth_status(phone):
    sql = (
        u"select status from couch_auth where phone = '{}' "
    )
    res = yield mysql_app_pool.get_one(sql.format(phone))
    raise gen.Return(res[0] if res else 0)


@gen.coroutine
def get_couch_base_info(phone):
    sql = (
        "select a.nick_name, a.avatar, b.* from app_user_info a LEFT JOIN couch_base_info b "
        "on a.phone = b.phone "
        u"where a.phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    auth_status = yield get_couch_auth_status(phone)
    info['car_type'] = info['car_type'].split(',')
    if info:
        info['auth_status'] = u"已认证" if auth_status else u"未认证"
    raise gen.Return(info)


@gen.coroutine
def get_driver_addr(phone):
    sql = (
        "select * from couch_driver_addr a "
        u"where a.phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if info:
        info['pics'] = info['pics'].split(',')
        for i in info['pics']:
            if i == "":
                info['pics'].remove(i)
    else:
        info = {}
    raise gen.Return(info)


@gen.coroutine
def set_driver_addr_pics(phone, pic_url):
    pic_str = ""
    for item in pic_url:
        pic_str += "{},".format(item)
    pic_str = pic_str[:-1]
    sql = (
        u"select * from couch_driver_addr where phone='{}'"
    )
    has_info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if has_info:
        sql = (
            u"update couch_driver_addr set pics='{}'where phone='{}' "
        )
        yield mysql_app_pool.update(sql.format(pic_str, phone))
    else:
        sql = (
            u"insert into couch_driver_addr (phone, pics) "
            u"values ('{}', '{}')"
        )
        yield mysql_app_pool.update(sql.format(phone, pic_str))
    raise gen.Return(1)


@gen.coroutine
def get_couch_introduce(phone):
    sql = (
        "select * from couch_introduce a "
        u"where a.phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if info:
        info['points'] = info['points'].split(',')
    raise gen.Return(info)


@gen.coroutine
def get_class_info_list(phone):
    sql = (
        "select * from couch_class_info a "
        u"where a.phone = '{}'"
    )
    info = yield mysql_app_pool.get_all(sql.format(phone), cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def get_couch_list(area, car_type, order, cnt):
    param = "limit {}".format(cnt) if cnt else ""
    sql = (
        "select b.avatar,  a.*, c.points from (couch_base_info a LEFT JOIN app_user_info b "
        "on a.phone = b.phone) LEFT JOIN couch_introduce c on c.phone=a.phone "
        "LEFT JOIN couch_auth d on a.phone = d.phone "
        "where a.has_card = 1 "
        "and d.status = 1 "
        u"and a.area = '{}' "
        u"and a.car_type like '%{}%' "
        "order by {} "
        "{}"
    )
    info = yield mysql_app_pool.get_all(sql.format(area, car_type, order, param), cursor='dict')
    for item in info:
        item['points'] = item['points'].split(',')[: 3]
        item['car_type'] = item['car_type'].split(',')
    raise gen.Return(info)


@gen.coroutine
def search_couch_list(area, keyword):
    sql = (
        "select b.avatar,  a.*, c.points from (couch_base_info a LEFT JOIN app_user_info b "
        "on a.phone = b.phone) LEFT JOIN couch_introduce c on c.phone=a.phone "
        "LEFT JOIN couch_auth d on a.phone = d.phone "
        "where a.has_card = 1 "
        "and d.status = 1 "
        u"and a.area = '{}' "
        u"and (a.nick_name like '%{}%' or a.school like '%{}%') "
        "order by score desc "
    )
    info = yield mysql_app_pool.get_all(sql.format(area, keyword, keyword), cursor='dict')
    for item in info:
        item['points'] = item['points'].split(',')[: 3]
        item['car_type'] = item['car_type'].split(',')
    raise gen.Return(info)


@gen.coroutine
def get_close_couch_list(area, longitude, latitude, car_type):
    sql = (
        "select b.avatar,  a.*, c.points from (couch_base_info a LEFT JOIN app_user_info b "
        "on a.phone = b.phone) LEFT JOIN couch_introduce c on c.phone=a.phone "
        "LEFT JOIN couch_auth d on a.phone = d.phone "
        "where a.has_card = 1 "
        "and d.status = 1 "
        u"and a.area = '{}' "
        u"and a.car_type like '%{}%' "
        "order by score desc"
    )
    info = yield mysql_app_pool.get_all(sql.format(area, car_type), cursor='dict')
    if longitude and latitude:
        for item in info:
            distance = yield get_distance_with_phone(item['phone'], longitude, latitude)
            item['distance'] = distance
            item['points'] = item['points'].split(',')[: 3]
            item['car_type'] = item['car_type'].split(',')
        info.sort(key=lambda x: x['distance'])
        info = info[:5]
    else:
        info = info[:5]
        for item in info:
            item['distance'] = -1
            item['points'] = item['points'].split(',')[: 3]
            item['car_type'] = item['car_type'].split(',')
    raise gen.Return(info)


@gen.coroutine
def get_my_follower(phone):
    sql = (
        "select b.phone, b.nick_name, b.avatar from couch_follow a LEFT JOIN app_user_info b "
        "on a.follow_phone = b.phone "
        "where a.couch_phone = '{}' "
        "order by a.add_time desc"
    )
    info = yield mysql_app_pool.get_all(sql.format(phone), cursor='dict')
    for item in info:
        item['is_vip'] = yield userm.check_phone_is_vip(item['phone'])
    raise gen.Return(info)


@gen.coroutine
def get_my_evaluate(phone, page_count, add_time):
    param = "and a.add_time < {}".format(add_time) if add_time else ""
    sql = (
        "select b.phone, b.nick_name, b.avatar, a.add_time, a.content, a.stars from couch_evaluate a "
        "LEFT JOIN app_user_info b on a.student_phone = b.phone "
        "where a.couch_phone = '{}' "
        "{} "
        "order by a.add_time desc "
        "limit {}"
    )
    info = yield mysql_app_pool.get_all(sql.format(phone, param, page_count), cursor='dict')
    for item in info:
        item['is_vip'] = yield userm.check_phone_is_vip(item['phone'])
    raise gen.Return(info)


@gen.coroutine
def get_pub_consult_list(add_time, area):
    param = "and add_time < {}".format(add_time) if add_time else ""
    sql = (
        "select b.nick_name, b.avatar, a.* from pub_consult a LEFT JOIN app_user_info b "
        u"on a.phone = b.phone where a.area='{}' {} order by a.add_time desc limit 20"
    )
    info = yield mysql_app_pool.get_all(sql.format(area, param), cursor='dict')
    for item in info:
        item['is_vip'] = yield userm.check_phone_is_vip(item['phone'])
    raise gen.Return(info)


@gen.coroutine
def get_consult_list(phone, add_time):
    param = "and a.add_time < {}".format(add_time) if add_time else ""
    sql = (
        "select b.nick_name, b.avatar, a.*, c.car_type, c.class_name from student_consult a , "
        "app_user_info b , couch_class_info c "
        "where a.couch_phone = '{}' "
        "and a.phone = b.phone "
        "and a.class_id = c.id "
        "{} "
        "order by a.add_time desc "
        "limit 20"
    )
    info = yield mysql_app_pool.get_all(sql.format(phone, param), cursor='dict')
    for item in info:
        item['is_vip'] = yield userm.check_phone_is_vip(item['phone'])
    raise gen.Return(info)


@gen.coroutine
def get_my_school(phone):
    sql = (
        "select * from user_school where phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    res = info if info else ""
    raise gen.Return(res)


@gen.coroutine
def set_my_school(phone, area, school):
    sql = (
        u"select * from user_school where phone='{}'"
    )
    has_info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if has_info:
        sql = (
            u"update user_school set area='{}', school='{}' where phone='{}' "
        )
        yield mysql_app_pool.update(sql.format(area, school, phone))
    else:
        sql = (
            u"insert into user_school (phone, area, school) "
            u"values ('{}', '{}', '{}')"
        )
        yield mysql_app_pool.update(sql.format(phone, area, school))
    raise gen.Return(1)


@gen.coroutine
def check_evalueate(phone, couch_phone):
    sql = "select 1 from couch_evaluate where couch_phone = '{}' and student_phone = '{}'"
    res1 = yield mysql_app_pool.get_one(sql.format(couch_phone, phone))
    res = 1 if res1 else 0
    raise gen.Return(res)


@gen.coroutine
def get_couch_detail(phone, couch_phone):
    sql = (
        "select b.avatar,  a.*, c.points from (couch_base_info a LEFT JOIN app_user_info b "
        "on a.phone = b.phone) LEFT JOIN couch_introduce c on c.phone=a.phone "
        "where a.phone = '{}' "
    )
    info = yield mysql_app_pool.get_one(sql.format(couch_phone), cursor='dict')
    if info:
        info['points'] = info['points'].split(',')[: 3]
        info['car_type'] = info['car_type'].split(',')
        info['auth_status'] = yield get_couch_auth_detail(couch_phone)
        info['addr'] = yield get_driver_addr(couch_phone)
        info['class_info'] = yield get_class_info_list(couch_phone)
        info['evaluate'] = yield get_my_evaluate(couch_phone, 5, '')
        info['is_follow'] = yield check_is_follow(phone, couch_phone)
        info['rank'] = yield get_couch_rank(couch_phone, info['area'])
        info['is_evaluate'] = yield check_evalueate(phone, couch_phone)

    raise gen.Return(info)


@gen.coroutine
def check_card_info(phone):
    sql_base = (
        "select 1 from couch_base_info where phone = '{}'"
    )
    info_base = yield mysql_app_pool.get_one(sql_base.format(phone))

    sql_addr = (
        "select 1 from couch_driver_addr where phone = '{}'"
    )
    info_addr = yield mysql_app_pool.get_one(sql_addr.format(phone))

    sql_introduce = (
        "select 1 from couch_introduce where phone = '{}'"
    )
    info_introduce = yield mysql_app_pool.get_one(sql_introduce.format(phone))
    info = dict(
        base=u"已完善" if info_base else u"待完善",
        addr=u"已完善" if info_addr else u"待完善",
        introduce=u"已完善" if info_introduce else u"待完善",
    )
    raise gen.Return(info)


@gen.coroutine
def set_couch_avatar_nick(phone, nick_name, avatar, current_time):
    if nick_name:
        sql = (
            u"update app_user_info set nick_name = '{}', flag=0, modify_time={} where phone='{}'"
        )
        yield mysql_app_pool.update(sql.format(nick_name, current_time, phone))
    elif avatar:
        sql = (
            u"update app_user_info set avatar = '{}', flag=0, modify_time={} where phone='{}'"
        )
        yield mysql_app_pool.update(sql.format(avatar, current_time, phone))

    raise gen.Return(1)


@gen.coroutine
def get_user_preference(phone):
    sql = (
        "select * from user_preference where phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    raise gen.Return(info)

from math import radians, cos, sin, asin, sqrt
def haversine(lon1, lat1, lon2, lat2):  # 经度1，纬度1，经度2，纬度2 （十进制度数）
    """
    Calculate the great circle distance between two points
    on the earth (specified in decimal degrees)
    """
    # 将十进制度数转化为弧度
    lon1, lat1, lon2, lat2 = map(radians, [lon1, lat1, lon2, lat2])

    # haversine公式
    dlon = lon2 - lon1
    dlat = lat2 - lat1
    a = sin(dlat / 2) ** 2 + cos(lat1) * cos(lat2) * sin(dlon / 2) ** 2
    c = 2 * asin(sqrt(a))
    r = 6371  # 地球平均半径，单位为公里
    res = c * r
    return float('%.1f' % res)


@gen.coroutine
def get_distance_with_phone(phone, longitude, latitude):
    sql = (
        "select longitude, latitude from couch_driver_addr where phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if info and info['longitude'] and info['latitude']:
        res = haversine(float(longitude), float(latitude), float(info['longitude']), float(info['latitude']))
    else:
        res = -1
    raise gen.Return(res)


@gen.coroutine
def get_couch_auth_detail(phone):
    sql = (
        "select * from couch_auth where phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    res = []
    if info:
        if info['jlz_status'] == 1:
            res.append(u"教练证")
        if info['sfz_status'] == 1:
            res.append(u"身份证")
        if info['jsz_status'] == 1:
            res.append(u"驾驶证")
        if info['xsz_status'] == 1:
            res.append(u"行驶证")
    raise gen.Return(res)


@gen.coroutine
def get_couch_rank(phone, area):
    info_list = yield get_couch_list(area, '', 'score desc', 0)
    res = -1
    for i, item in enumerate(info_list):
        if phone == item['phone']:
            res = i + 1
            break
    raise gen.Return(res)


@gen.coroutine
def set_couch_auth_pic(phone, jlz_url, sfz_url, xsz_url, jsz_url):
    current_time = timetool.get_timestamp()
    sql = (
        u"select * from couch_auth where phone='{}'"
    )
    has_info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if has_info:
        param = ""
        if jlz_url:
            param += ",jlz_pic='{}'".format(jlz_url)
        if sfz_url:
            param += ",sfz_pic='{}'".format(sfz_url)
        if xsz_url:
            param += ",xsz_pic='{}'".format(xsz_url)
        if jsz_url:
            param += ",jsz_pic='{}'".format(jsz_url)
        sql = "update couch_auth set pubtime='{}' {} where phone='{}'"
        info = yield mysql_app_pool.update(sql.format(current_time, param, phone))
    else:
        sql = (
            u"insert into couch_auth (phone, jlz_pic, sfz_pic, xsz_pic, jsz_pic, pubtime) "
            u"values ('{}', '{}', '{}', '{}', '{}', {})"
        )
        info = yield mysql_app_pool.insert_one(sql.format(phone, jlz_url, sfz_url, xsz_url, jsz_url, current_time))
    raise gen.Return(info)


@gen.coroutine
def get_couch_auth_pic(phone):
    sql = "select * from couch_auth where phone='{}'"
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def add_couch_score(phone, desc, type, score):
    news_score = 0
    if phone:
        add = yield update_couch_score_2_mysql(phone, score)
        if add:
            yield save_couch_score_log(phone, desc, type, score)
    raise gen.Return(news_score)


@gen.coroutine
def get_my_couch(phone):
    sql = (
        "select couch_phone from couch_follow where follow_phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def get_my_couch_info(phone, longitude, latitude):
    couch_phone = yield get_my_couch(phone)
    if couch_phone:
        sql = (
            "select b.avatar,  a.*, c.points from (couch_base_info a LEFT JOIN app_user_info b "
            "on a.phone = b.phone) LEFT JOIN couch_introduce c on c.phone=a.phone "
            "where a.has_card = 1 "
            u"and a.phone = '{}' "
        )
        info = yield mysql_app_pool.get_one(sql.format(couch_phone['couch_phone']), cursor='dict')
        info['points'] = info['points'].split(',')[: 3]
        info['car_type'] = info['car_type'].split(',')
        if longitude and latitude:
            distance = yield get_distance_with_phone(couch_phone['couch_phone'], longitude, latitude)
        else:
            distance = -1
        info['distance'] = distance
        info['auth'] = yield get_couch_auth_status(couch_phone['couch_phone'])
    else:
        info = {}
    raise gen.Return(info)


@gen.coroutine
def check_is_open_bmt(phone):
    current_time = timetool.get_timestamp()
    if phone:
        sql = (
            u"select 1 from couch_bmt where phone='{}' and start < {} and end > {}"
        )
        vip = yield mysql_app_pool.get_one(sql.format(phone, current_time, current_time))
        res = 1 if vip else 0
    else:
        res = 0
    raise gen.Return(res)


@gen.coroutine
def get_last_pub_consult_with_phone(phone):
    sql = (
        "select max(id) as id from pub_consult where phone = '{}' "
    )
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def get_last_consult_with_phone(phone, couch_phone):
    sql = (
        "select id from student_consult where phone = '{}' and couch_phone = '{}' order by id desc limit 1"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone, couch_phone), cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def get_share_pic():
    sql = (
        "select * from share_pic limit 1"
    )
    info = yield mysql_app_pool.get_one(sql, cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def get_addr_area_with_phone(phone):
    sql = (
        "select area from couch_driver_addr where phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone))
    res = info[0] if info else ""
    raise gen.Return(res)


@gen.coroutine
def get_student_cnt():
    sql = (
        "select distinct(follow_phone) from couch_follow "
    )
    info = yield mysql_app_pool.get_all(sql, cursor='dict')
    raise gen.Return(len(info))


@gen.coroutine
def get_couch_circle_avatar():
    sql = (
        "select distinct b.avatar from app_circle_info a, app_user_info b  where couch = 1 "
        "and a.phone=b.phone order by a.create_time desc limit 6"
    )
    info = yield mysql_app_pool.get_all(sql, cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def user_add_driver_school(phone, area, school, current_time):
    sql = (
        u"insert into user_driver_school (area, school, add_time, add_user) values ('{}', '{}', {}, '{}')"
    )
    info = yield mysql_app_pool.insert_one(sql.format(area, school, current_time, phone))
    raise gen.Return(info)


@gen.coroutine
def check_couch_today_added(phone):
    today = timetool.get_current_date()
    start = timetool.convert_date_to_timestamp(today + ' 00:00:00')
    end = timetool.convert_date_to_timestamp(today + ' 23:59:59')
    if phone:
        sql = (
            u"select 1 from couch_score_log where phone='{}' and pubtime >= {} and pubtime <= {} and type='share'"
        )
        added = yield mysql_app_pool.get_one(sql.format(phone, start, end))
        res = True if added else False
    else:
        res = True
    raise gen.Return(res)


@gen.coroutine
def get_phonecode_with_phone(phone):
    sql = (
        u"select phone_code from app_user_info where phone = '{}'"
    )
    info = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    res = info['phone_code'] if info else ""
    raise gen.Return(res)
